<?php

class Community_model extends CI_Model {

    function __construct() {
        parent::__construct();
    }

    function communities() {
        return $this->db->get('gemeenten');
    }

    function community_years() {
        $this->db->select('jaar');
        $this->db->distinct();
        $this->db->from('gemeenten_cijfers');
        $this->db->order_by("jaar", "desc");
        return $this->db->get();
    }

    function maxyears() {
        $this->db->select('DISTINCT(jaar)', 'maxjaar');
        $this->db->select_max('jaar', 'maxjaar');
        $this->db->from('gemeenten_cijfers');
        return $this->db->get();
    }

    function getProvince($community) {
        $this->db->select('Provincie');
        $this->db->from('gemeenten');
        $this->db->where('Naam', $community);
        return $this->db->get();
    }

    function community_session($community, $year) {


        $this->db->select('CBS');
        $this->db->from('gemeenten');
        $this->db->where('Naam', $community);
        $data = $this->db->get();


        $cbs = $data->row()->CBS;


        $this->db->select('jaar, Inwoners, Sociaal, Centrum');
        $this->db->where('jaar', $year);
        $this->db->where('gemeente', $cbs);
        $this->db->from('gemeenten_cijfers');

        return $this->db->get();
    }

    function bigSelection($year, $social, $central, $population) {

        $popu = $population;
        $between_popu = explode("-", $popu, 2);

        $this->db->select('g.Naam, gc.jaar, gc.Inwoners, gc.Sociaal, gc.Centrum, g.Provincie', FALSE);
        $this->db->where('gc.jaar', $year, FALSE);

        if ($population == '< 10000') {
            $this->db->where('gc.Inwoners <', 10000);
        } else if ($population == '120000 >') {
            $this->db->where('gc.Inwoners >', 120000);
        } else {
            $this->db->where("gc.Inwoners BETWEEN " . $between_popu[0] . " AND " . $between_popu[1]);
        }

        $this->db->where('gc.Sociaal', "'$social'", FALSE);
        $this->db->where('gc.Centrum', "'$central'", FALSE);
        $this->db->where('gc.gemeente', 'g.CBS', FALSE);
        $this->db->from('gemeenten_cijfers AS gc, gemeenten AS g', FALSE);

        return $this->db->get();
    }

    function socialCentralSelection($year, $social, $central) {



        $this->db->select('g.Naam, gc.Inwoners, gc.jaar, gc.Sociaal, gc.Centrum, g.Provincie', FALSE);
        $this->db->where('gc.jaar', $year, FALSE);
        $this->db->where('gc.Sociaal', "'$social'", FALSE);
        $this->db->where('gc.Centrum', "'$central'", FALSE);
        $this->db->where('gc.gemeente', 'g.CBS', FALSE);
        $this->db->from('gemeenten_cijfers AS gc, gemeenten AS g', FALSE);

        return $this->db->get();
    }

    function socialPopulationSelection($year, $social, $population) {

        $popu = $population;
        $between_popu = explode("-", $popu, 2);

        $this->db->select('g.Naam, gc.jaar, gc.Inwoners, gc.Sociaal, gc.Centrum, g.Provincie', FALSE);
        $this->db->where('gc.jaar', $year, FALSE);

        if ($population == '< 10000') {
            $this->db->where('gc.Inwoners <', 10000);
        } else if ($population == '120000 >') {
            $this->db->where('gc.Inwoners >', 120000);
        } else {
            $this->db->where("gc.Inwoners BETWEEN " . $between_popu[0] . " AND " . $between_popu[1]);
        }

        $this->db->where('gc.Sociaal', "'$social'", FALSE);
        $this->db->where('gc.gemeente', 'g.CBS', FALSE);
        $this->db->from('gemeenten_cijfers AS gc, gemeenten AS g', FALSE);

        return $this->db->get();
    }

    function centralPopulationSelection($year, $central, $population) {

        $popu = $population;
        $between_popu = explode("-", $popu, 2);

        $this->db->select('g.Naam, gc.jaar, gc.Inwoners, gc.Sociaal, gc.Centrum, g.Provincie', FALSE);
        $this->db->where('gc.jaar', $year, FALSE);

        if ($population == '< 10000') {
            $this->db->where('gc.Inwoners <', 10000);
        } else if ($population == '120000 >') {
            $this->db->where('gc.Inwoners >', 120000);
        } else {
            $this->db->where("gc.Inwoners BETWEEN " . $between_popu[0] . " AND " . $between_popu[1]);
        }
        $this->db->where('gc.Centrum', "'$central'", FALSE);
        $this->db->where('gc.gemeente', 'g.CBS', FALSE);
        $this->db->from('gemeenten_cijfers AS gc, gemeenten AS g', FALSE);

        return $this->db->get();
    }

    function socialSelection($year, $social) {



        $this->db->select('g.Naam, gc.jaar, gc.Inwoners, gc.Sociaal, gc.Centrum, g.Provincie', FALSE);
        $this->db->where('gc.jaar', $year, FALSE);
        $this->db->where('gc.Sociaal', "'$social'", FALSE);
        $this->db->where('gc.gemeente', 'g.CBS', FALSE);
        $this->db->from('gemeenten_cijfers AS gc, gemeenten AS g', FALSE);

        return $this->db->get();
    }

    function centralSelection($year, $central) {

        $this->db->select('g.Naam, gc.jaar, gc.Inwoners, gc.Sociaal, gc.Centrum, g.Provincie', FALSE);
        $this->db->where('gc.jaar', $year, FALSE);
        $this->db->where('gc.Centrum', "'$central'", FALSE);
        $this->db->where('gc.gemeente', 'g.CBS', FALSE);
        $this->db->from('gemeenten_cijfers AS gc, gemeenten AS g', FALSE);

        return $this->db->get();
    }

    function populationSelection($year, $population) {

        $popu = $population;
        $between_popu = explode("-", $popu, 2);

        $this->db->select('g.Naam, gc.jaar, gc.Inwoners, gc.Sociaal, gc.Centrum, g.Provincie', FALSE);
        $this->db->where('gc.jaar', $year, FALSE);

        if ($population == '< 10000') {
            $this->db->where('gc.Inwoners <', 10000);
        } else if ($population == '120000 >') {
            $this->db->where('gc.Inwoners >', 120000);
        } else {
            $this->db->where("gc.Inwoners BETWEEN " . $between_popu[0] . " AND " . $between_popu[1]);
        }

        $this->db->where('gc.gemeente', 'g.CBS', FALSE);
        $this->db->from('gemeenten_cijfers AS gc, gemeenten AS g', FALSE);

        return $this->db->get();
    }

    function search($year, $search) {


        $this->db->select('g.Naam, gc.jaar, gc.Inwoners, gc.Sociaal, gc.Centrum, g.Provincie', FALSE);
        $this->db->where('gc.jaar', $year, FALSE);
        $this->db->where('g.Naam', "'$search'", FALSE);

        $this->db->where('gc.gemeente', 'g.CBS', FALSE);
        $this->db->from('gemeenten_cijfers AS gc, gemeenten AS g', FALSE);

        return $this->db->get();
    }

    function searchList($year) {


        $this->db->select('g.Naam, gc.jaar, gc.Inwoners, gc.Sociaal, gc.Centrum, g.Provincie', FALSE);
        $this->db->where('gc.jaar', $year, FALSE);

        $this->db->where('gc.gemeente', 'g.CBS', FALSE);
        $this->db->from('gemeenten_cijfers AS gc, gemeenten AS g', FALSE);

        return $this->db->get();
    }

}

?>
